package surreal.triviamaster.database;

import surreal.triviamaster.beans.Answer;
import surreal.triviamaster.beans.Category;
import surreal.triviamaster.beans.Flag;
import surreal.triviamaster.beans.LikelyAnswer;
import surreal.triviamaster.beans.Question;
import surreal.triviamaster.utilities.QuizResults;
import surreal.triviamaster.utilities.TriviaMasterApplication;
import android.app.Application;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class LocalCache {
	private static final String DB_NAME = "events.db";
	private static final int DB_VERSION = 46;

	public static final String TABLE_USER = "User";
	public static final String COLUMN_USER_HASH = "hash";
	public static final String COLUMN_USER_CURRENT_QUIZ_INDEX = "currentQuizIndex";
	public static final String COLUMN_USER_ID = "id";
	public static final String COLUMN_USER_CURRENT_QUIZ_CATEGORY = "currentQuizCategory";
	public static final String COLUMN_USER_CURRENT_QUIZ_LENGTH = "currentQuizLength";
	public static final String[] USER_ALL_COLUMNS = { COLUMN_USER_HASH,
			COLUMN_USER_CURRENT_QUIZ_INDEX, COLUMN_USER_ID,
			COLUMN_USER_CURRENT_QUIZ_CATEGORY, COLUMN_USER_CURRENT_QUIZ_LENGTH };

	public static final String TABLE_CATEGORIES = "Categories";
	public static final String COLUMN_CATEGORIES_ID = "id";
	public static final String COLUMN_CATEGORIES_CATEGORY = "category";
	public static final String[] CATEGORIES_ALL_COLUMNS = {
			COLUMN_CATEGORIES_ID, COLUMN_CATEGORIES_CATEGORY };

	public static final String TABLE_ACCEPTABLEANSWERS = "Answers";
	public static final String COLUMN_ACCEPTABLEANSWERS_ID = "id";
	public static final String COLUMN_ACCEPTABLEANSWERS_ANSWER = "answer";
	public static final String COLUMN_ACCEPTABLEANSWERS_QUESTION_ID = "questionId";
	public static final String[] ACCEPTABLEANSWERS_ALL_COLUMNS = {
			COLUMN_ACCEPTABLEANSWERS_ID, COLUMN_ACCEPTABLEANSWERS_ANSWER,
			COLUMN_ACCEPTABLEANSWERS_QUESTION_ID };
	
	//making sure LikelyAnswers is the exact same as Answers
	public static final String TABLE_LIKELYANSWERS = "LikelyAnswers";
	public static final String COLUMN_LIKELYANSWERS_ID = COLUMN_ACCEPTABLEANSWERS_ID;
	public static final String COLUMN_LIKELYANSWERS_ANSWER = COLUMN_ACCEPTABLEANSWERS_ANSWER;
	public static final String COLUMN_LIKELYANSWERS_QUESTION_ID = COLUMN_ACCEPTABLEANSWERS_QUESTION_ID;
	public static final String COLUMN_LIKELYANSWERS_ANSWER_ID="answerId";
	public static final String[] LIKELYANSWERS_ALL_COLUMNS = {
		COLUMN_LIKELYANSWERS_ID, 
		COLUMN_LIKELYANSWERS_ANSWER,
		COLUMN_LIKELYANSWERS_QUESTION_ID,
		COLUMN_LIKELYANSWERS_ANSWER_ID};

	public static final String TABLE_QUESTIONS = "Questions";
	public static final String COLUMN_QUESTIONS_ID = "id";
	public static final String COLUMN_QUESTIONS_QUESTION = "question";
	public static final String COLUMN_QUESTIONS_QUIZ_INDEX = "quizIndex";
	public static final String COLUMN_QUESTIONS_ANSWERED_CORRECTLY = "answeredCorrectly";
	public static final String COLUMN_QUESTIONS_FLAG = "flagged";
	public static final String COLUMN_QUESTIONS_GUESS = "guess";
	public static final String COLUMN_QUESTIONS_HINT = "hint";
	public static final String COLUMN_QUESTIONS_USED_HINT = "usedHint";
	public static final String COLUMN_QUESTIONS_USED_MC = "usedMC";

	public static final int QUESTIONS_NOT_FLAGGED = 0;
	public static final int QUESTIONS_FLAGGED_QUESTION = 1;
	public static final int QUESTIONS_FLAGGED_ANSWER = 2;
	public static final int QUESTIONS_FLAGGED_BOTH = 3;

	public static final String[] QUESTIONS_ALL_COLUMNS = { 
			COLUMN_QUESTIONS_ID,
			COLUMN_QUESTIONS_QUESTION, 
			COLUMN_QUESTIONS_QUIZ_INDEX,
			COLUMN_QUESTIONS_ANSWERED_CORRECTLY, 
			COLUMN_QUESTIONS_FLAG,
			COLUMN_QUESTIONS_GUESS,
			COLUMN_QUESTIONS_HINT,
			COLUMN_QUESTIONS_USED_HINT,
			COLUMN_QUESTIONS_USED_MC};

	public static final String TABLE_FLAGS = "Flags";
	public static final String COLUMN_FLAGS_ID = "id";
	public static final String COLUMN_FLAGS_QUESTION_ID = "questionId";
	public static final String COLUMN_FLAGS_LISTED = "listedFlag";
	public static final String COLUMN_FLAGS_MINE_CORRECT = "mineCorrectFlag";
	public static final String COLUMN_FLAGS_INAPPROPRIATE = "inapropriate";
	public static final String COLUMN_FLAGS_INCOMPLETE = "incomplete";
	public static final String COLUMN_FLAGS_USER_ANSWER = "userAnswer";
	public static final String COLUMN_FLAGS_OTHER = "other";
	public static final String[] FLAGS_ALL_COLUMNS = { COLUMN_FLAGS_ID,
			COLUMN_FLAGS_QUESTION_ID, COLUMN_FLAGS_LISTED,
			COLUMN_FLAGS_MINE_CORRECT, COLUMN_FLAGS_INAPPROPRIATE,
			COLUMN_FLAGS_INCOMPLETE, COLUMN_FLAGS_USER_ANSWER,
			COLUMN_FLAGS_OTHER };

	private static LocalCache soleInstance = null;

	private SQLiteDatabase db;

	private LocalCache(Context context) {
		OpenHelper helper = new OpenHelper(context, DB_NAME, DB_VERSION);
		db = helper.getWritableDatabase();
	}

	/**
	 * Makes this a singleton
	 */

	public static LocalCache getDatabase(Application app) {
		if (soleInstance == null) {
			soleInstance = new LocalCache(app);
		}
		return soleInstance;
	}

	/**
	 * Refreshes the local database with the given cats
	 */

	public void refreshCategories(Category[] cats) {
		deleteAllCategories();
		insertCategories(cats);
	}

	/**
	 * Inserts the given cats to the local database
	 */
	private void insertCategories(Category[] cats) {

		for (int i = 0; i < cats.length; i++) {
			insertCategory(cats[i]);
			Log.d("localcache", cats[i].toString());
		}

	}

	/**
	 * Inserts a single category into the local database
	 * 
	 * @return The new Row ID
	 */
	private long insertCategory(Category event) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_CATEGORIES_ID, event.getId());
		map.put(COLUMN_CATEGORIES_CATEGORY, event.getCategory());
		return db.insert(TABLE_CATEGORIES, null, map);
	}

	/*
	 * Deletes all the cats in the local database
	 * 
	 * return The Number of deleted rows
	 */

	private int deleteAllCategories() {
		return db.delete(TABLE_CATEGORIES, null, null);
	}

	public Category[] getAllCategories() {

		Cursor cursor = db.query(TABLE_CATEGORIES, CATEGORIES_ALL_COLUMNS,
				null, null, null, null, COLUMN_CATEGORIES_CATEGORY);

		Category[] cats = new Category[cursor.getCount()];
		int i = 0;
		while (cursor.moveToNext()) {
			cats[i] = new Category(cursor.getInt(0), cursor.getString(1));
			i++;
		}
		return cats;
	}

	/*
	 * Open Helper
	 */
	private class OpenHelper extends SQLiteOpenHelper {
		public OpenHelper(Context context, String name, int version) {
			super(context, name, null, version);

		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.d("LocalCache", "On Create DB");

			String q = "CREATE TABLE " + TABLE_CATEGORIES + "(" + "  "
					+ COLUMN_CATEGORIES_ID + " INTEGER PRIMARY KEY," + "  "
					+ COLUMN_CATEGORIES_CATEGORY + " TEXT" + ");";
			db.execSQL(q);

			/*
			 * We want to maintain this table to that a new user hash is not
			 * created every local db update
			 */
			q = "CREATE TABLE IF NOT EXISTS " + TABLE_USER + "(" + "  "
					+ COLUMN_USER_HASH + " TEXT," + "  "
					+ COLUMN_USER_CURRENT_QUIZ_INDEX + " INTEGER," + "  "
					+ COLUMN_USER_ID + " INTEGER PRIMARY KEY," + "  "
					+ COLUMN_USER_CURRENT_QUIZ_CATEGORY + " INTEGER,"  
					+ COLUMN_USER_CURRENT_QUIZ_LENGTH + " INTEGER" + ");";
			db.execSQL(q);

			q = "CREATE TABLE " + TABLE_QUESTIONS + "(" + "  "
					+ COLUMN_QUESTIONS_ID + " INTEGER PRIMARY KEY," + "  "
					+ COLUMN_QUESTIONS_QUESTION + " TEXT," + "  "
					+ COLUMN_QUESTIONS_QUIZ_INDEX + " INTEGER," + "  "
					+ COLUMN_QUESTIONS_ANSWERED_CORRECTLY + " INTEGER," + "  "
					+ COLUMN_QUESTIONS_FLAG + " INTEGER," + " " 
					+ COLUMN_QUESTIONS_GUESS + " TEXT DEFAULT ''," 
					+ COLUMN_QUESTIONS_HINT + " TEXT DEFAULT ''," 
					+ COLUMN_QUESTIONS_USED_HINT + " INTEGER, " 
					+ COLUMN_QUESTIONS_USED_MC + " INTEGER " 
					+ ");";
			db.execSQL(q);

			q = "CREATE TABLE " + TABLE_ACCEPTABLEANSWERS + "(" + "  "
					+ COLUMN_ACCEPTABLEANSWERS_ID + " INTEGER PRIMARY KEY,"
					+ "  " + COLUMN_ACCEPTABLEANSWERS_QUESTION_ID + " INTEGER,"
					+ "  " + COLUMN_ACCEPTABLEANSWERS_ANSWER + " TEXT" + ");";
			db.execSQL(q);
			
			q = "CREATE TABLE " + TABLE_LIKELYANSWERS + "(" + "  "
					+ COLUMN_LIKELYANSWERS_ID + " INTEGER PRIMARY KEY,"
					+ "  " + COLUMN_LIKELYANSWERS_QUESTION_ID + " INTEGER,"
					+ "  " + COLUMN_LIKELYANSWERS_ANSWER + " TEXT," 
					+ "  " + COLUMN_LIKELYANSWERS_ANSWER_ID + " INTEGER "+ ");";
			db.execSQL(q);

			q = "CREATE TABLE " + TABLE_FLAGS + "(" + "  " + COLUMN_FLAGS_ID
					+ " INTEGER PRIMARY KEY," + "  " + COLUMN_FLAGS_QUESTION_ID
					+ " INTEGER," + "  " + COLUMN_FLAGS_LISTED + " INTEGER,"
					+ "  " + COLUMN_FLAGS_MINE_CORRECT + " INTEGER," + "  "
					+ COLUMN_FLAGS_INAPPROPRIATE + " INTEGER," + "  "
					+ COLUMN_FLAGS_INCOMPLETE + " INTEGER," + "  "
					+ COLUMN_FLAGS_USER_ANSWER + " TEXT," + "  "
					+ COLUMN_FLAGS_OTHER + " TEXT" + ");";
			db.execSQL(q);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.d("LocalCache", "On Upgrade DB");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUESTIONS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCEPTABLEANSWERS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_LIKELYANSWERS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_FLAGS);
			onCreate(db);
		}
	}

	/**
	 * Inserts the given Questions and Acceptable Answers into the local cache.  Then resets the quiz length,
	 * and current user quiz index.
	 * @param fAs 
	 */
	public void refreshQuestions(Question[] questions, Answer[] aAs, LikelyAnswer[] fAs) {
		
		// delete old questions
		deleteAllQuestions();
		// insert new questions
		insertQuestions(questions);
		//setting the new quiz length
		setUserQuizLength(questions.length);
		
		//refreshing the aAs for these questions
		refreshAnswers(aAs, fAs);
		//resetting the quiz index
		refreshUserQuizIndex();
	}

	/**
	 * Inserts the given questions to the local database. Labels then 1-10
	 */
	private void insertQuestions(Question[] q) {
		for (int i = 0; i < q.length; i++) {
			insertQuestion(q[i], i + 1);
			Log.d("LocalCache Insert Question", q[i].toString());
		}
	}

	/**
	 * Inserts a single question into the local database
	 * 
	 * return The new Row ID
	 */
	private long insertQuestion(Question q, int quizIndex) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_QUESTIONS_ID, q.getId());
		map.put(COLUMN_QUESTIONS_QUESTION, q.getQuestion());
		map.put(COLUMN_QUESTIONS_QUIZ_INDEX, quizIndex);
		map.put(COLUMN_QUESTIONS_FLAG, QUESTIONS_NOT_FLAGGED);
		map.put(COLUMN_QUESTIONS_HINT, q.getHint());
		return db.insert(TABLE_QUESTIONS, null, map);
	}

	/**
	 * Deletes all questions
	 * 
	 * return Number of rows deleted
	 */
	private int deleteAllQuestions() {
		return db.delete(TABLE_QUESTIONS, null, null);
	}

	/*
	 * Deletes all acceptable answers
	 * 
	 * return Number of rows deleted
	 */
	private int deleteAllAnswers(String tableName) {
		return db.delete(tableName, null, null);
	}
	/**
	 * Deletes the old answers and inserts the new given answers
	 * @param aAs The given answers
	 * @param fAs 
	 */
	private void refreshAnswers(Answer[] aAs, LikelyAnswer[] fAs) {
		// flush old aAs
		// delete old aAs
		deleteAllAnswers(TABLE_ACCEPTABLEANSWERS);
		deleteAllAnswers(TABLE_LIKELYANSWERS);
		// insert new aAs
		insertAnswers(aAs);
		insertLikelyAnswers(fAs);

	}

	private void insertLikelyAnswers(LikelyAnswer[] fAs) {
		for (int i = 0; i < fAs.length; i++) {
			Log.d("Insert: ",""+fAs[i].toString());
			insertLikelyAnswer(fAs[i]);
		}
		
	}

	private void insertLikelyAnswer(LikelyAnswer likelyAnswer) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_LIKELYANSWERS_ID, likelyAnswer.getId());
		map.put(COLUMN_LIKELYANSWERS_QUESTION_ID, likelyAnswer.getQuestionId());
		map.put(COLUMN_LIKELYANSWERS_ANSWER, likelyAnswer.getAnswer());
		map.put(COLUMN_LIKELYANSWERS_ANSWER_ID, likelyAnswer.getAnswerid());
		db.insert(TABLE_LIKELYANSWERS, null, map);
		
	}

	/**
	 * Inserts the given answers into the local cache
	 * @param tableName Which Answers table to insert into
	 * @param aAs Answers to be added
	 */
	private void insertAnswers(Answer[] answers) {
		for (int i = 0; i < answers.length; i++) {
			Log.d("Insert: ",""+answers[i].toString());
			insertAnswer(answers[i]);
		}
	}

	/**
	 * Inserts a single Answer into the local cache
	 * @param tableName Which answer table to insert into
	 * @param answer The answer to insert
	 */
	private void insertAnswer(Answer answer) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_ACCEPTABLEANSWERS_ID, answer.getId());
		map.put(COLUMN_ACCEPTABLEANSWERS_QUESTION_ID, answer.getQuestionId());
		map.put(COLUMN_ACCEPTABLEANSWERS_ANSWER, answer.getAnswer());
		db.insert(TABLE_ACCEPTABLEANSWERS, null, map);
	}

	/*
	 * Gets the users hash code
	 * 
	 * return The hash code or "null"
	 */
	public String getUserHash() {
		Cursor cursor = db.query(TABLE_USER, USER_ALL_COLUMNS, null, null,
				null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getString(0);
		}
		return "null";
	}

	/**
	 * Inserts this user's hash and userId into the local cache
	 * @param hash This User's hash code
	 * @param userId This User's userId
	 * @return The id of the inserted row into the Users table
	 */
	public long insertUser(String hash, String userId) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_USER_HASH, hash);
		map.put(COLUMN_USER_ID, userId);
		map.put(COLUMN_USER_CURRENT_QUIZ_INDEX, 0);
		Log.d("LocalCache", "Hash: " + hash + " UserId: " + userId);
		return db.insert(TABLE_USER, null, map);
	}

	/**
	 * Sets the local user to be on the first question
	 */
	private void refreshUserQuizIndex() {
		ContentValues map = new ContentValues();
		map.put(COLUMN_USER_CURRENT_QUIZ_INDEX, 0);
		// There should only be one entry so no need for a where clause
		db.update(TABLE_USER, map, null, null);
	}

	/**
	 * Returns the current quiz questions. Requires the user table to have one
	 * and only one record in it
	 * 
	 * @return The current question
	 */
	public Question getCurrentQuizQuestion() {

		Question curQuestion = getQuestionIndex(getUserQuizIndex());

		Log.d("LocalCache", "Quiz Index: " + getUserQuizIndex());
		Log.d("LocalCache", "Q: " + curQuestion.toString());

		Answer[] aAs = getQuestionAnswers(curQuestion.getId());
		LikelyAnswer[] fAs = getQuestionLikelyAnswers(curQuestion.getId());
		try {
			curQuestion.setaAs(aAs);
			
		} catch (Exception e) {
			Log.d("Local Cache aAs invalid", TriviaMasterApplication.ERROR_INVALID_CHARACTERS);
			e.printStackTrace();
		}
		curQuestion.setfAs(fAs);
		
		return curQuestion;
	}
	public LikelyAnswer[] getQuestionLikelyAnswers(int id) {
		Cursor cursor = db.query(TABLE_LIKELYANSWERS,
				LIKELYANSWERS_ALL_COLUMNS,
				COLUMN_LIKELYANSWERS_QUESTION_ID + " = " + id + " ", null,
				null, null, null);

		LikelyAnswer[] answers = new LikelyAnswer[cursor.getCount()];
		int i = 0;
		while (cursor.moveToNext()) {
			answers[i] = new LikelyAnswer(cursor.getInt(0), cursor.getInt(2),cursor.getInt(3),cursor.getString(1));
			i++;
		}
		return answers;
	}

	/**
	 * Get all the answers from the given questionId
	 * @param tableName The table the question is in
	 * @param questionId The questionId of the question answers to be returned
	 * @return A list of all the answers
	 */
	public Answer[] getQuestionAnswers(int questionId) {

		Cursor cursor = db.query(TABLE_ACCEPTABLEANSWERS,
				ACCEPTABLEANSWERS_ALL_COLUMNS,
				COLUMN_ACCEPTABLEANSWERS_QUESTION_ID + " = " + questionId + " ", null,
				null, null, null);

		Answer[] answers = new Answer[cursor.getCount()];
		int i = 0;
		while (cursor.moveToNext()) {
			answers[i] = new Answer(cursor.getInt(0), cursor.getInt(2),	cursor.getString(1));
			i++;
		}
		return answers;
	}

	/**
	 * Requires this question to exist in the local db
	 * 
	 * @param userQuizIndex
	 *            The column value of the question in the local cache
	 * @return The current quiz question without the aAs
	 */
	private Question getQuestionIndex(int userQuizIndex) {
		Cursor cursor = db.query(TABLE_QUESTIONS, QUESTIONS_ALL_COLUMNS,
				COLUMN_QUESTIONS_QUIZ_INDEX + "=" + userQuizIndex, null, null,
				null, null);
		if (cursor.moveToNext()) {
			try {
				Log.d("LocalCache", "id: " + cursor.getInt(0) + " q: " + cursor.getString(1) + " c: " +(cursor.getInt(3) == 1)
						+ " qi: " + cursor.getInt(2) + " f: " + cursor.getInt(4) + " g: " + cursor.getString(5) + " h: " +cursor.getString(6));
				
				Question tmp =new Question(cursor.getInt(0), cursor.getString(1),
						cursor.getInt(3) == 1, cursor.getInt(2), cursor.getInt(4), cursor.getString(5),
						cursor.getString(6), cursor.getInt(7), cursor.getInt(8));
				
				return tmp;
			} catch (Exception e) {
				Log.d("Utilities Json to Question", TriviaMasterApplication.ERROR_INVALID_CHARACTERS);
				e.printStackTrace();
				return null;
			}
		} else {
			Log.d("LocalCache", "getQuestionIndex error");
			return null;
		}
	}

	/**
	 * Will return the current quiz index
	 */
	public int getUserQuizIndex() {
		Cursor cursor = db.query(TABLE_USER, USER_ALL_COLUMNS, null, null,
				null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getInt(1);
		}
		Log.d("LocalCache", "getUserQuizIndex error");
		return -1;
	}

	/**
	 * Increases the user quiz index by one
	 * 
	 * @return Returns the new index
	 */

	public int incrementUserQuizIndex() {
		int quizIndex = getUserQuizIndex() + 1;
		ContentValues map = new ContentValues();
		map.put(COLUMN_USER_CURRENT_QUIZ_INDEX, quizIndex);
		// There should only be one entry so no need for a where clause
		db.update(TABLE_USER, map, null, null);

		return quizIndex;
	}
	
	/**
	 * Decreases the user index by one
	 * @return Returns the new index
	 */
	public int decrementUserQuizIndex() {
		int quizIndex = getUserQuizIndex() - 1;
		ContentValues map = new ContentValues();
		map.put(COLUMN_USER_CURRENT_QUIZ_INDEX, quizIndex);
		// There should only be one entry so no need for a where clause
		db.update(TABLE_USER, map, null, null);

		return quizIndex;
	}

	public int getUserId() {
		Cursor cursor = db.query(TABLE_USER, USER_ALL_COLUMNS, null, null,
				null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getInt(2);
		}
		return -1;
	}

	/**
	 * Sets the current quiz question to the given value
	 * @param answer 
	 * @param val The current quiz question will receive this value
	 * @param COLUMN_QUESTIONS_ANSWER 
	 * @return The number of rows affected
	 */
	public int setCurrentQuizQuestion(String answer, boolean val) {
		int curQuizIndex = getUserQuizIndex();
		int boolVal = 0;

		if (val) {
			boolVal = 1;
		}

		Log.d("LocalCache", "Boolval: " + boolVal + " curQuizIndex:  "
				+ curQuizIndex);

		ContentValues map = new ContentValues();
		map.put(COLUMN_QUESTIONS_GUESS, answer);
		map.put(COLUMN_QUESTIONS_ANSWERED_CORRECTLY, "" + boolVal);
		return db.update(TABLE_QUESTIONS, map, COLUMN_QUESTIONS_QUIZ_INDEX + " = "
				+ curQuizIndex, null);
	}

	/**
	 * Returns a object QuizResults which contains information about the
	 * completed quiz
	 * 
	 * @return QuizResults object
	 */

	public QuizResults getQuizResults() {
		// select all questions that are not to be uploaded
		Cursor cursor = db.query(TABLE_QUESTIONS, QUESTIONS_ALL_COLUMNS, null,
				null, null, null, null);

		int numFlagged = 0;
		int numCorrect = 0;
		int numQuestions = 0;
		Log.d("LocalCache", "getQuizResults");

		while (cursor.moveToNext()) {
			numQuestions++;

			if (cursor.getInt(4) != QUESTIONS_NOT_FLAGGED) {
				numFlagged++;
			}
			if (cursor.getInt(3) == 1) {
				numCorrect++;
			}
		}
		return new QuizResults(numFlagged, numCorrect, numQuestions, getCurrentQuizScore());
	}

	/*
	 * return the current category
	 */
	public String getCurrentQuizCategoryString() {
		Cursor cursor = db.query(TABLE_USER, USER_ALL_COLUMNS, null, null,
				null, null, null);
		if (cursor.moveToNext()) {
			return getCategory(cursor.getInt(3));
		}
		Log.d("LocalCache",
				"getCurrentCategoryString error or no category choosen");
		return null;
	}

	private String getCategory(int catId) {
		Cursor cursor = db.query(TABLE_CATEGORIES, CATEGORIES_ALL_COLUMNS,
				COLUMN_CATEGORIES_ID + " = " + catId, null, null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getString(1);
		}
		Log.d("LocalCache", "getCategory error, catId: " + catId);
		return null;
	}

	public void setCurrentQuizCategory(int catId) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_USER_CURRENT_QUIZ_CATEGORY, catId);
		// There should only be one entry so no need for a where clause
		db.update(TABLE_USER, map, null, null);
	}
	/**
	 * 
	 * @return The current quiz category or 0 if no category was chosen
	 */
	public int getCurrentQuizCategory() {
		Cursor cursor = db.query(TABLE_USER, USER_ALL_COLUMNS, null, null,
				null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getInt(3);
		}
		Log.d("LocalCache", "getCurrentCategory error");
		return 0;
	}

	/**
	 * Gets all the questions from the local cache that are in the quiz
	 * 
	 * @return List of questions
	 */

	public Question[] getQuizQuestions() {
		Cursor cursor = db.query(TABLE_QUESTIONS, QUESTIONS_ALL_COLUMNS, null,
				null, null, null, null);
		Question[] qList = new Question[cursor.getCount()];
		int i = 0;
		while (cursor.moveToNext()) {
			try {
				qList[i] = new Question(cursor.getInt(0), cursor.getString(1),
						cursor.getInt(3) == 1, cursor.getInt(2), cursor.getInt(4), cursor.getString(5),cursor.getString(6),cursor.getInt(7),cursor.getInt(8));
			} catch (Exception e) {
				Log.d("Local Cache", TriviaMasterApplication.ERROR_INVALID_CHARACTERS);
				e.printStackTrace();
				return null;
			}
			Log.d("LocalCache", "GetQuizQuestions "+qList[i].toString());
			i++;
		}
		//getting the fAs and adding them to the questions
		for(int j=0;j<qList.length;j++) {
			qList[j].setfAs(getQuestionLikelyAnswers(qList[j].getId()));
		}
		return qList;
	}

	/**
	 * Delete all the quiz questions
	 */
	public int deleteQuizQuestions() {
		return db.delete(TABLE_QUESTIONS, null, null);
	}

	/**
	 * Inserts a flag into the local cache associated with the current question
	 * 
	 * @param listedFlag
	 *            1 if one of the answers are incorrect
	 * @param mineCorrectFlag
	 *            1 if the user's answer is correct
	 * @param inapropriate
	 *            1 if the question is inappropriate for all ages
	 * @param incomplete
	 *            1 if the question is not a complete question
	 * @param yourAnswer
	 *            The users given answer
	 * @param other
	 *            Other comments from the user
	 * @return The id of the Flag created in the local cache
	 */
	public long flagCurrentQuestion(int listedFlag, int mineCorrectFlag,
			int inappropriate, int incomplete, String yourAnswer, String other) {
		if (inappropriate == 1 || incomplete == 1) {
			flagCurrentQuestion(QUESTIONS_FLAGGED_QUESTION);
		} else {
			flagCurrentQuestion(QUESTIONS_FLAGGED_ANSWER);
		}
		ContentValues map = new ContentValues();
		map.put(COLUMN_FLAGS_QUESTION_ID, "" + getCurrentQuizQuestion().getId());
		map.put(COLUMN_FLAGS_LISTED, listedFlag);
		map.put(COLUMN_FLAGS_MINE_CORRECT, mineCorrectFlag);
		map.put(COLUMN_FLAGS_INAPPROPRIATE, inappropriate);
		map.put(COLUMN_FLAGS_INCOMPLETE, incomplete);
		map.put(COLUMN_FLAGS_USER_ANSWER, yourAnswer);
		map.put(COLUMN_FLAGS_OTHER, other);
		long colId = db.insert(TABLE_FLAGS, null, map);
		Log.d("Flag current Question", "id: " + colId + " ");
		return colId;
	}

	/**
	 * Flags the table Questions with the given flag index
	 * 
	 * @param flaggedIndex
	 *            Flagged question index
	 */
	private void flagCurrentQuestion(int flaggedIndex) {
		int curQuizIndex = getUserQuizIndex();

		ContentValues map = new ContentValues();
		map.put(COLUMN_QUESTIONS_FLAG, "" + flaggedIndex);
		db.update(TABLE_QUESTIONS, map, COLUMN_QUESTIONS_QUIZ_INDEX + " = "
				+ curQuizIndex, null);
		Log.d("LocalCache flag current question", "Cur Quiz Index: "
				+ curQuizIndex);

	}

	/**
	 * Returns the flag with all of its data
	 * 
	 * @param questionId
	 *            The questionId for the flag to be returned
	 * @return The flag that corresponds with the given questionId. Null if
	 *         nothing was found.
	 */
	public Flag getFlag(int questionId) {
		Cursor cursor = db.query(TABLE_FLAGS, FLAGS_ALL_COLUMNS,
				COLUMN_FLAGS_QUESTION_ID + " = " + questionId, null, null,
				null, null);

		if (cursor.moveToNext()) {
			return new Flag(cursor.getInt(0), cursor.getInt(1),
					cursor.getInt(2), cursor.getInt(3), cursor.getInt(4),
					cursor.getInt(5), cursor.getString(6), cursor.getString(7));
		}
		Log.d("LocalCache getFlag", "getFlag error");
		return null;
	}
	/**
	 * 
	 * @return Returns the length of the current quiz
	 */
	public int getUserQuizLength() {
		Cursor cursor = db.query(TABLE_USER, USER_ALL_COLUMNS, null, null,
				null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getInt(4);
		}
		Log.d("LocalCache", "getUserQuizLength error");
		return 0;
	}
	
	/**
	 * Sets the length of the current quiz to the given value in the Users table in the local cache
	 * @param length The length of the current quiz
	 */
	private void setUserQuizLength(int length) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_USER_CURRENT_QUIZ_LENGTH, length);
		// There should only be one entry so no need for a where clause
		db.update(TABLE_USER, map, null, null);
		
	}

	/**
	 * Updates the Question table.  Sets this question's usedHint to the value
	 * @param value The value given to usedHint
	 * @param id The questionId
	 */
	public void setQuestionHint(int value, int id) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_QUESTIONS_USED_HINT, value);
		db.update(TABLE_QUESTIONS, map, COLUMN_QUESTIONS_ID + "="+id, null);
	}

	/**
	 * Updates the Question table.  Sets this question's usedMC to the value
	 * @param value The value given to usedMC
	 * @param id The questionId
	 */
	public void setQuestionMC(int value, int id) {
		ContentValues map = new ContentValues();
		map.put(COLUMN_QUESTIONS_USED_MC, value);
		db.update(TABLE_QUESTIONS, map, COLUMN_QUESTIONS_ID + "="+id, null);
		
	}
	

	public int getCurrentQuizScore() {
		Cursor cursor = db.query(TABLE_QUESTIONS, QUESTIONS_ALL_COLUMNS, null,
				null, null, null, null);

		int points = 0;
		Log.d("LocalCache", "getCurrentQuizScore");

		while (cursor.moveToNext()) {
			//if the answer is correct
			if (cursor.getInt(3) == 1) {
				if(cursor.getInt(7)==1) {
					points+=TriviaMasterApplication.POINT_VALUE_HINT;
				} else if (cursor.getInt(8)==1) {
					points+=TriviaMasterApplication.POINT_VALUE_MC;
				} else {
					points+=TriviaMasterApplication.POINT_VALUE_FULL;
				}
			}
			
		}
		return points;
	}
}